package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import ModelLayer.Delivery;

public class DbDelivery implements IFDBDelivery{
	
	private Connection con;
	
	public DbDelivery(){
		con = DbConnection.getInstance().getDBcon();
	}
	
	@Override
	public int getNextDeliveryId() {
		return GetMax.getMaxId("Select max(id) from Delivery");
	}
	
	@Override
	public ArrayList<Delivery> getAllDelivery() {
		return miscWhere("");
	}
	
	@Override
	public Delivery getDeliveryById(int id) {
		String wClause = "id = '" + id + "'";
		System.out.println("Search in Delivery " + wClause);
		return singleWhere(wClause);
	}
	
	@Override
	public ArrayList<Delivery> getDeliveryByStatus(String status) {
		String wClause = "status = '" + status.toLowerCase() + "'";
		System.out.println("Search in Delivery " + wClause);
		return miscWhere(wClause);
	}
	
	@Override
	public int insertDelivery(Delivery delivery) throws Exception {
		//call to get the next id number
		int nextID = GetMax.getMaxId("Select max(id) from Delivery");
		nextID = nextID + 1;
		System.out.println("next id = " +  nextID);
		int rc = -1;
		
		java.sql.Date sqlCreationDate = new java.sql.Date(delivery.getDeliveryDate().getTime());
		
		String insertQuery="INSERT INTO Delivery(id, status, deliveryDate";
		String valueQuery = " VALUES('"+
				nextID  + "','"  +
				delivery.getDeliveryState().toLowerCase() + "','"  +
				sqlCreationDate + "'";

		String query = insertQuery + ") " + valueQuery + ")";

		System.out.println("insert Delivery: " + query);
		try
		{ // insert new delivery
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}//end try
		catch(SQLException ex){
			System.out.println("Delivery not created");
			throw new Exception("Delivery is not inserted correctly");
		}
		return(rc);
	}

	@Override
	public int updateDelivery(Delivery delivery) {
		int rc=-1;

		java.sql.Date sqlCreationDate = new java.sql.Date(delivery.getDeliveryDate().getTime());
		
		String query="UPDATE Delivery SET " +
				"status = '" + delivery.getDeliveryState().toLowerCase() + "', " +
				"deliveryDate = '" + sqlCreationDate + "' " +
				"WHERE id = '" + delivery.getDeliveryId() + "'";
		
		System.out.println("update Delivery: " + query);
		try
		{ //Update Delivery
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}//end try	
		catch(Exception ex){
			System.out.println("Update exception in Delivery db: "+ex);
		}
		return(rc);
	}

	@Override
	public int deleteDelivery(Delivery delivery) {
		int rc=-1;

		String query="DELETE FROM Delivery WHERE id = '" +
				delivery.getDeliveryId() + "'";
		System.out.println("delete Delivery: " + query);
		try
		{ // delete from supplier
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}//end try	
		catch(Exception ex){
			System.out.println("Delete exception in Delivery db: "+ex);
		}
		return(rc);
	}
	
	private ArrayList<Delivery> miscWhere(String wClause)
	{
		ResultSet results;
		ArrayList<Delivery> list = new ArrayList<Delivery>();	

		String query =  buildQuery(wClause);

		try{ // read the delivery from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);


			while(results.next()){
				Delivery delObj = new Delivery();
				delObj = buildDelivery(results);	
				list.add(delObj);	
			}//end while
			stmt.close();  

		}//end try	
		catch(Exception e){
			System.out.println("Query exception - select delivery: "+e);
			e.printStackTrace();
		}
		return list;
	}
	
	//Singlewhere to select a delivery 	
		private Delivery singleWhere(String wClause)
		{
			ResultSet results;
			Delivery deliveryObj = new Delivery();
			
		
				String query =  buildQuery(wClause);
				System.out.println(query);
				try{ // read the delivery from the database
					Statement stmt = con.createStatement();
					stmt.setQueryTimeout(5);
					results = stmt.executeQuery(query);

					if( results.next() ){
						deliveryObj = buildDelivery(results);
						stmt.close();
					}		
					else{ //no delivery was found
						deliveryObj = null;
					}
				}
				catch(Exception e){
					System.out.println("Query exception - select delivery: "+e);
				}

			return deliveryObj;
		}
		
		//method to build the query for Delivery
		private String buildQuery(String wClause)
		{
			String query="SELECT id, status, deliveryDate FROM Delivery";

			if (wClause.length()>0)
				query=query+" WHERE "+ wClause;

			return query;
		}
		
		//method to build a Delivery object
		private Delivery buildDelivery(ResultSet results)
		{   
			Delivery deliveryObj = new Delivery();
			try{ // the columns from the table Delivery are used
				deliveryObj.setDeliveryId(results.getInt("id"));
				deliveryObj.setDeliveryState(results.getString("status"));
				deliveryObj.setDeliveryDate(results.getDate("deliveryDate"));
			}
			catch(Exception e)
			{
				System.out.println("error in building the delivery object");
			}
			return deliveryObj;
		}
}
